
package de.justphil.tcg.tcgandroidclient.content;

import java.io.File;

import de.justphil.tcg.tcgandroidclient.Example;
import de.justphil.tcg.tcgandroidclient.util.StringTools;
import de.justphil.tcg.tcgandroidclient.util.TCGLog;
import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DH extends SQLiteOpenHelper {
	
	/* Database */
	public static final int VERSION = 51;
	public static final String DATABASE = "tcg";
	/* Tables */
	public static final String T_CARDS = "cards";
	public static final String T_DECKS_TO_CARDS = "decks_to_cards";
	public static final String T_CARDS_TO_PROPERTIES = "cards_to_properties";
	public static final String T_CARD_INSTANCES = "card_instances";
	public static final String T_DECKS = "decks";
	// public static final String T_PROPERTIES = "properties";
	/* Columns */
	public static final String C_PLAYER_ID = "player_id";
	public static final String C_CARD_ID = "card_id";
	public static final String C_CARD_TYPE = "card_type";
	public static final String C_CARD_NAME = "card_name";
	public static final String C_CARD_LIFE = "life";
	public static final String C_CARD_ATTACK = "attack";
	public static final String C_CARD_DEFENSE = "defense";
	public static final String C_CARD_AGILITY = "agility";
	public static final String C_DECK_ID = "deck_id";
	public static final String C_DECK_NAME = "deck_name";
	public static final String C_PROPERTY_TYPE = "property_type";
	public static final String C_UPDATED = "updated";
	private static final String C_ROW_ID = "_id";
	/* Create table statements */
	private static final String CREATE_TABLE_CARDS = StringTools
			.getString(new Object[] {
					"CREATE TABLE ", T_CARDS, " ( ", C_CARD_TYPE, " INTEGER PRIMARY KEY, ",
					C_CARD_NAME, " VARCHAR (100), ", C_CARD_LIFE, " INTEGER, ", C_CARD_ATTACK,
					" INTEGER, ", C_CARD_DEFENSE, " INTEGER, ", C_CARD_AGILITY, " INTEGER, ",
					C_UPDATED, " INTEGER ", ")" });
	private static final String CREATE_TABLE_CARDS_TO_PROPERTIES = StringTools
			.getString(new Object[] {
					"CREATE TABLE ", T_CARDS_TO_PROPERTIES, " (", C_ROW_ID,
					" INTEGER PRIMARY KEY, ", C_CARD_TYPE, " INTEGER, ", C_PROPERTY_TYPE,
					" INTEGER ", ")" });
	private static final String CREATE_TABLE_CARDS_TO_DECKS = StringTools.getString(new Object[] {
			"CREATE TABLE ", T_DECKS_TO_CARDS, " (", C_ROW_ID, " INTEGER PRIMARY KEY, ", C_CARD_ID,
			" INTEGER, ", C_DECK_ID, " INTEGER ", ")" });
	private static final String CREATE_TABLE_REPOSITORY = StringTools.getString(new Object[] {
			"CREATE TABLE ", T_CARD_INSTANCES, " (", C_CARD_ID, " INTEGER PRIMARY KEY, ",
			C_CARD_TYPE, " INTEGER , ", C_PLAYER_ID, " INTEGER", ")" });
	private static final String CREATE_TABLE_DECKS = StringTools.getString(new Object[] {
			"CREATE TABLE ", T_DECKS, " (", C_DECK_ID, " INTEGER PRIMARY KEY, ", C_DECK_NAME,
			" VARCHAR (100), ", C_PLAYER_ID, " INTEGER ", ")" });
	/* Folders */
	public static final String FOLDER_CARDS = "cards";
	public static final String FOLDER_PROPERTIES = "properties";
	public static final String FOLDER_IMAGES = "img";
	/* The Uri matcher */
	public static final TCGUM UM = new TCGUM();
	private Context context;
	
	public String getFilePath(String folder, int identifier) {
	
		File cacheDir = null;
		// if (Environment.MEDIA_MOUNTED.equals(Environment.getExternalStorageState())) {
		// cacheDir = context.getExternalCacheDir();
		// TCGLog.e(new Object[] {
		// "External cacheDir could not be created. State: ",
		// Environment.getExternalStorageState() });
		// }
		if (cacheDir == null) {
			cacheDir = context.getCacheDir();
		}
		return StringTools.getString(new Object[] {
				cacheDir.getPath(), "/", FOLDER_IMAGES, "/", folder, "/", identifier, ".png" });
		// TODO: if images are deleted, or the external storage is removed, they have to be
		// reloaded!
	}
	
	public DH(Context context) {
	
		super(context, DATABASE, null, VERSION);
		this.context = context;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
	
		onUpgrade(db, 0, VERSION);
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	
		TCGLog.w(new Object[] {
				"Updating database and file cache from version ", oldVersion, " to ", newVersion,
				", and DELETING ALL DATA!" });
		{
			try {
				String query = "DROP TABLE " + T_CARD_INSTANCES;
				TCGLog.i(new Object[] {
						"Executing: ", query });
				db.execSQL(query);
			}
			catch (SQLException e) {
				TCGLog.w(new Object[] {
					e.getMessage() });
			}
			try {
				String query = "DROP TABLE " + T_CARDS;
				TCGLog.i(new Object[] {
						"Executing: ", query });
				db.execSQL(query);
			}
			catch (SQLException e) {
				TCGLog.w(new Object[] {
					e.getMessage() });
			}
			try {
				String query = "DROP TABLE " + T_DECKS_TO_CARDS;
				TCGLog.i(new Object[] {
						"Executing: ", query });
				db.execSQL(query);
			}
			catch (SQLException e) {
				TCGLog.w(new Object[] {
					e.getMessage() });
			}
			try {
				String query = "DROP TABLE " + T_CARDS_TO_PROPERTIES;
				TCGLog.i(new Object[] {
						"Executing: ", query });
				db.execSQL(query);
			}
			catch (SQLException e) {
				TCGLog.w(new Object[] {
					e.getMessage() });
			}
			try {
				String query = "DROP TABLE " + T_DECKS;
				TCGLog.i(new Object[] {
						"Executing: ", query });
				db.execSQL(query);
			}
			catch (SQLException e) {
				TCGLog.w(new Object[] {
					e.getMessage() });
			}
		}
		{
			TCGLog.i(new Object[] {
					"Executing: ", CREATE_TABLE_CARDS });
			db.execSQL(CREATE_TABLE_CARDS);
			TCGLog.i(new Object[] {
					"Executing: ", CREATE_TABLE_CARDS_TO_PROPERTIES });
			db.execSQL(CREATE_TABLE_CARDS_TO_PROPERTIES);
			TCGLog.i(new Object[] {
					"Executing: ", CREATE_TABLE_CARDS_TO_DECKS });
			db.execSQL(CREATE_TABLE_CARDS_TO_DECKS);
			TCGLog.i(new Object[] {
					"Executing: ", CREATE_TABLE_DECKS });
			db.execSQL(CREATE_TABLE_DECKS);
			TCGLog.i(new Object[] {
					"Executing: ", CREATE_TABLE_REPOSITORY });
			db.execSQL(CREATE_TABLE_REPOSITORY);
		}
		{
			for (File f : context.getCacheDir().listFiles()) {
				deleteTree(f);
			}
		}
		{
			File imgFolder = new File(StringTools.getString(new Object[] {
					context.getCacheDir().getPath(), "/", FOLDER_IMAGES }));
			TCGLog.i(new Object[] {
					"Creating folder: ", imgFolder.getAbsolutePath() });
			imgFolder.mkdir();
			File cardImgFolder = new File(StringTools.getString(new Object[] {
					imgFolder.getPath(), "/", FOLDER_CARDS }));
			TCGLog.i(new Object[] {
					"Creating folder: ", cardImgFolder.getAbsolutePath() });
			cardImgFolder.mkdir();
			File propertyImgFolder = new File(StringTools.getString(new Object[] {
					imgFolder.getPath(), "/", FOLDER_PROPERTIES }));
			TCGLog.i(new Object[] {
					"Creating folder: ", propertyImgFolder.getAbsolutePath() });
			propertyImgFolder.mkdir();
		}
	}
	
	public static void deleteTree(File f) {
	
		if (f.isDirectory()) {
			for (File child : f.listFiles()) {
				deleteTree(child);
			}
		}
		f.delete();
		TCGLog.i(new Object[] {
				"Deleted File: ", f.getAbsolutePath() });
	}
	
	public static String appendWhereClause(String existingWhereClause, long id, String column) {
	
		String whereClause;
		if (existingWhereClause == null) {
			whereClause = "";
		}
		else {
			whereClause = existingWhereClause + " AND ";
		}
		return StringTools.getString(new Object[] {
				whereClause, column, " = ", id });
	}
}
